package com.easy.common.util;

import java.util.List;

import com.easy.auth.domain.Userinfo;
import com.easy.base.domain.EObject;
import com.easy.common.web.FilterRule;
import com.easy.common.web.FilterUtil;

public class SqlUtil {
	public static class SqlInfo {
		private String sql;
		private String[] values;

		public SqlInfo(String sql, String[] values) {
			super();
			this.sql = sql;
			this.values = values;
		}

		public String getSql() {
			return sql;
		}

		public String[] getValues() {
			return values;
		}
	}

	private SqlUtil() {

	}

	public static SqlInfo getInsertSql(EObject o) {
		List<String> fields = BeanUtil.getFields(o);
		if (o.getOid() == null || "".equals(o.getOid())) {
			fields.remove("oid");
		}

		StringBuffer buffer = new StringBuffer("INSERT INTO ");

		buffer.append(o.getTableName()).append(getFieldStr(fields));

		buffer.append(" VALUES").append(getQuestionMark(fields.size()));

		String sql = buffer.toString();
		String[] values = BeanUtil.getFieldValues(o, fields);

		return new SqlInfo(sql, values);
	}

	public static SqlInfo getUpdateSql(EObject o) {
		List<String> fields = BeanUtil.getFields(o);
		fields.remove("oid");

		StringBuffer buffer = new StringBuffer("UPDATE ").append(
				o.getTableName()).append(" SET ");
		buffer.append(getFieldQuestionMarkStr(fields));
		buffer.append(" WHERE oid = ?");

		String sql = buffer.toString();
		fields.add("oid");
		String[] values = BeanUtil.getFieldValues(o, fields);

		return new SqlInfo(sql, values);
	}

	public static String getSelectByIdSql(EObject o) {
		StringBuffer buffer = new StringBuffer("SELECT * FROM ")
				.append(o.getTableName()).append(" WHERE oid = '")
				.append(o.getOid()).append("'");

		return buffer.toString();
	}

	public static String getSelectAllLikeValueSql(EObject o) {
		StringBuffer buffer = new StringBuffer("SELECT * FROM ")
				.append(o.getTableName()).append(" WHERE 1=1 ")
				.append(getFieldLikeValue(o));

		return buffer.toString();
	}

	public static String getSelectAllEqualValueSql(EObject o) {
		StringBuffer buffer = new StringBuffer("SELECT * FROM ")
				.append(o.getTableName()).append(" WHERE 1=1 ")
				.append(getFieldEqualValue(o));

		return buffer.toString();
	}

	public static String getSelectAllLikeTextSql(EObject o, String text) {
		StringBuffer buffer = new StringBuffer("SELECT * FROM ").append(o
				.getTableName());
		if (text != null && !"".equals(text.trim())) {
			buffer.append(" WHERE ").append(getFieldLikeText(o, text));
		}
		return buffer.toString();
	}

	public static String getDeleteSql(EObject o) {
		StringBuffer buffer = new StringBuffer("DELETE FROM ")
				.append(o.getTableName()).append(" WHERE oid = '")
				.append(o.getOid()).append("'");

		return buffer.toString();
	}

	public static String getCreateTableDefaultSql(EObject o) {
		List<String> fields = BeanUtil.getFields(o);

		StringBuffer buffer = new StringBuffer("CREATE TABLE ").append(
				o.getTableName()).append(" (\n");
		buffer.append(getFieldCreateTableStr(fields));
		buffer.append(");");

		return buffer.toString();
	}

	private static String getFieldEqualValue(EObject o) {
		List<String> fields = BeanUtil.getFields(o);
		int len = fields.size();

		StringBuffer buffer = new StringBuffer("");
		for (int i = 0; i < len; ++i) {
			String name = fields.get(i);
			String value = BeanUtil.invokeGet(o, name);

			if (value != null && !value.trim().equals("")) {
				buffer.append(" AND ").append(name).append(" = '")
						.append(value).append("'");
			}
		}

		return buffer.toString();
	}

	private static String getFieldLikeValue(EObject o) {
		List<String> fields = BeanUtil.getFields(o);
		int len = fields.size();

		StringBuffer buffer = new StringBuffer("");
		for (int i = 0; i < len; ++i) {
			String name = fields.get(i);
			String value = BeanUtil.invokeGet(o, name);

			if (value != null && !value.trim().equals("")) {
				buffer.append(" AND ").append(name).append(" LIKE '%")
						.append(value).append("%'");
			}
		}

		return buffer.toString();
	}

	private static String getFieldStr(List<String> fields) {
		int len = fields.size();

		StringBuffer buffer = new StringBuffer("(");
		for (int i = 0; i < len; ++i) {
			String name = fields.get(i);

			if (i == len - 1) {
				buffer.append(name);
			} else {
				buffer.append(name).append(", ");
			}
		}

		buffer.append(")");

		return buffer.toString();
	}

	private static String getFieldCreateTableStr(List<String> fields) {
		int len = fields.size();

		StringBuffer buffer = new StringBuffer("");
		for (int i = 0; i < len; ++i) {
			String name = fields.get(i);

			if (i == len - 1) {
				buffer.append("\t").append(name).append(" VARCHAR(128)\n");
			} else {
				buffer.append("\t").append(name).append(" VARCHAR(128),\n");
			}
		}
		return buffer.toString();
	}

	private static String getFieldQuestionMarkStr(List<String> fields) {
		int len = fields.size();

		StringBuffer buffer = new StringBuffer();
		for (int i = 0; i < len; ++i) {
			String name = fields.get(i);

			if (i == len - 1) {
				buffer.append(name).append(" = ?");
			} else {
				buffer.append(name).append(" = ?").append(", ");
			}
		}

		return buffer.toString();
	}

	private static String getQuestionMark(int len) {
		StringBuffer buffer = new StringBuffer("(");
		for (int i = 0; i < len; ++i) {
			if (i == len - 1) {
				buffer.append("?");
			} else {
				buffer.append("?, ");
			}
		}

		buffer.append(")");

		return buffer.toString();
	}

	/**
	 * 对嵌套结果集进行模糊查询
	 * 
	 * @param o
	 * @param text
	 * @return
	 */
	public static String getSelectAllLikeTextInQuerySql(EObject o, String text) {
		StringBuffer buffer = new StringBuffer("SELECT * FROM ").append("(");
		buffer.append(getSelectAllEqualValueSql(o));
		buffer.append(") as t");
		if (text != null && !"".equals(text.trim())) {
			buffer.append(" WHERE ").append(
					SqlUtil.getFieldLikeText(o, text, "t"));
		}
		return buffer.toString();
	}

	/**
	 * 给模糊条件各个字段加上别名
	 * 
	 * @param o
	 * @param text
	 * @param alias
	 * @return
	 */
	private static String getFieldLikeText(EObject o, String text, String alias) {
		List<String> fields = BeanUtil.getFields(o);
		int len = fields.size();
		boolean first = true;

		StringBuffer buffer = new StringBuffer("");
		for (int i = 0; i < len; ++i) {
			String name = fields.get(i);

			if (text != null && !text.trim().equals("")) {
				String aliasStr = "";
				if (alias != null) {
					aliasStr = alias + ".";
				}

				if (first) {
					buffer.append(aliasStr).append(name).append(" LIKE '%")
							.append(text).append("%'");
					first = false;
				} else {
					buffer.append(" OR ").append(aliasStr).append(name)
							.append(" LIKE '%").append(text).append("%'");
				}
			}
		}
		return buffer.toString();
	}

	private static String getFieldLikeText(EObject o, String text) {
		return getFieldLikeText(o, text, null);
	}

	/**
	 * 根据Userinfo的各个属性拼接查询条件--全文模糊查询
	 * 
	 * @param o
	 * @return
	 */
	public static String getFieldLikeValue(Userinfo o, String searchText) {
		List<String> fields = BeanUtil.getFields(o);
		int len = fields.size();

		StringBuffer buffer = new StringBuffer("");
		for (int i = 0; i < len; ++i) {
			String name = fields.get(i);

			if (i == 0) {
				buffer.append(" AND ").append(name).append(" LIKE '%")
						.append(searchText).append("%'");
			} else {
				buffer.append(" OR ").append(name).append(" LIKE '%")
						.append(searchText).append("%'");
			}
		}

		return buffer.toString();
	}

	/**
	 * 数据表格过滤查询语句
	 * 
	 * @param o
	 * @param filterRule
	 * @return
	 */
	public static String getConditionsByOptions(EObject o, String filterRule) {
		StringBuffer buffer = new StringBuffer("SELECT * FROM ")
				.append(o.getTableName()).append(" WHERE 1=1 ")
				.append(getConditionsByOp(filterRule));
		return buffer.toString();
	}

	/**
	 * 根据过滤条件拼接查询条件
	 * 
	 * @param filterRule
	 *            过滤条件
	 * @return
	 */
	private static String getConditionsByOp(String filterRule) {
		StringBuffer sqlCon = new StringBuffer("");
		List<FilterRule> filterRuleList = FilterUtil.getFilterList(filterRule);
		for (FilterRule item : filterRuleList) {
			if (!item.getValue().equals("")) {
				sqlCon.append("and ");
				String option = item.getOp();
				sqlCon.append(item.getField());
				switch (option) {
				case "contains":
					sqlCon.append(" like '%");
					sqlCon.append(item.getValue());
					sqlCon.append("%'");
					break;
				case "equal":
					sqlCon.append("='");
					sqlCon.append(item.getValue());
					sqlCon.append("'");
					break;
				case "notequal":
					sqlCon.append("<>'");
					sqlCon.append(item.getValue());
					sqlCon.append("'");
					break;
				case "beginwith":
					sqlCon.append(" like '");
					sqlCon.append(item.getValue());
					sqlCon.append("%'");
					break;
				case "endwith":
					sqlCon.append(" like '%");
					sqlCon.append(item.getValue());
					sqlCon.append("'");
					break;
				case "less":
					sqlCon.append("<'");
					sqlCon.append(item.getValue());
					sqlCon.append("'");
					break;
				case "lessorequal":
					sqlCon.append("<='");
					sqlCon.append(item.getValue());
					sqlCon.append("'");
					break;
				case "greater":
					sqlCon.append(">'");
					sqlCon.append(item.getValue());
					sqlCon.append("'");
					break;
				case "greaterorequal":
					sqlCon.append(">='");
					sqlCon.append(item.getValue());
					sqlCon.append("'");
					break;
				default:
					sqlCon.append(" like '%");
					sqlCon.append(item.getValue());
					sqlCon.append("%'");
					break;
				}
			}
		}
		return sqlCon.toString();
	}
}
